package com.sog.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.sog.common.DBUtils;
import com.sog.entity.Goods;


public class GoodsDaoImpl implements GoodsDaoI<Goods> {

	public boolean update(Goods g) throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "UPDATE good  SET goods_name=?,store_number=?,good_buy_price=?,good_sell_price=?,good_sale_price=?,if_onsale=?, alert_num=?,producing_area=?,effect=?,weight=?,color=?,describes=?,series_id=?"
				
				+ " where goods_id=?";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sql);
	ps.setString(1,g.getGoods_name() );
	ps.setInt(2,g.getStore_number() );
	ps.setBigDecimal(3, g.getGood_buy_price());
	ps.setBigDecimal(4, g.getGood_sell_price());
	ps.setBigDecimal(5, g.getGood_sale_price());
	ps.setBoolean(6, g.isIf_onsale());
	ps.setInt(7, g.getAlert_num());
	ps.setString(8, g.getProducing_area());
	ps.setString(9, g.getEffect());
	ps.setString(10, g.getWeight());
	ps.setString(11, g.getColor());
	ps.setString(12, g.getDescribe());
	ps.setInt(13, g.getSeries_id());
	ps.setInt(14, g.getGoods_id());
		if (ps.executeUpdate() > 0) {
			return true;
		}
		DBUtils.close(conn, ps, null);
		
		return false;
		
	}

	public boolean insert(Goods g) throws SQLException {
		Connection conn = DBUtils.getConnection();
		String sql = "insert into  good  (goods_name,store_number,good_buy_price,good_sell_price,good_sale_price,if_onsale, alert_num,producing_area,effect,weight,color,describes,series_id)  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
			
		PreparedStatement ps = conn.prepareStatement(sql);
	ps.setString(1,g.getGoods_name() );
	ps.setInt(2,g.getStore_number() );
	ps.setBigDecimal(3, g.getGood_buy_price());
	ps.setBigDecimal(4, g.getGood_sell_price());
	ps.setBigDecimal(5, g.getGood_sale_price());
	ps.setBoolean(6, g.isIf_onsale());
	ps.setInt(7, g.getAlert_num());
	ps.setString(8, g.getProducing_area());
	ps.setString(9, g.getEffect());
	ps.setString(10, g.getWeight());
	ps.setString(11, g.getColor());
	ps.setString(12, g.getDescribe());
	ps.setInt(13, g.getSeries_id());
	
		if (ps.executeUpdate() > 0) {
			return true;
		}
		DBUtils.close(conn, ps, null);
		
		return false;
		
	}

	public List<Goods> selectAll() throws SQLException {
		Connection conn = DBUtils.getConnection();
		String sql = "SELECT * FROM good";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sql);
		//将数据库中的记录转换为对象
		List<Goods>  list=new ArrayList<Goods>();
		//结果集
		ResultSet rs=ps.executeQuery();
		while (rs.next()) {
			Goods g=new Goods();
			g.setGoods_id(rs.getInt(1));
			g.setGoods_name(rs.getString(2));
			g.setStore_number(rs.getInt(3));
			g.setGood_buy_price(rs.getBigDecimal(4));
			g.setGood_sell_price(rs.getBigDecimal(5));
			g.setGood_sale_price(rs.getBigDecimal(6));
			g.setIf_onsale(rs.getBoolean(7));
			g.setAlert_num(rs.getInt(8));
			g.setProducing_area(rs.getString(9));
			g.setEffect(rs.getString(10));
			g.setWeight(rs.getString(11));
			g.setColor(rs.getString(12));
			g.setDescribe(rs.getString(13));
			g.setSeries_id(rs.getInt(14));
			list.add(g);
		}
		DBUtils.close(conn, ps, null);
		return list;
	}

	public List<Goods> selectWhere(String whereSql) throws SQLException {
		
		Connection conn = DBUtils.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("SELECT * ");
		sb.append("FROM good ");
		sb.append(" WHERE 1=1 ");
		sb.append(whereSql);
		System.out.println(sb.toString());
		
		PreparedStatement ps = conn.prepareStatement(sb.toString());	
		List<Goods>  list= new ArrayList<Goods>();
		ResultSet rs=ps.executeQuery();
		while (rs.next()) {
			Goods g=new Goods();
			g.setGoods_id(rs.getInt(1));
			g.setGoods_name(rs.getString(2));
			g.setStore_number(rs.getInt(3));
			g.setGood_buy_price(rs.getBigDecimal(4));
			g.setGood_sell_price(rs.getBigDecimal(5));
			g.setGood_sale_price(rs.getBigDecimal(6));
			g.setIf_onsale(rs.getBoolean(7));
			g.setAlert_num(rs.getInt(8));
			g.setProducing_area(rs.getString(9));
			g.setEffect(rs.getString(10));
			g.setWeight(rs.getString(11));
			g.setColor(rs.getString(12));
			g.setDescribe(rs.getString(13));
			g.setSeries_id(rs.getInt(14));
			list.add(g);
		
		
		}
		DBUtils.close(conn, ps, rs);
		return list;
		
	
	}

	public boolean delete(int id) throws SQLException {
		Connection conn = DBUtils.getConnection();
		String sql = "DELETE FROM good WHERE goods_id=?";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		if (ps.executeUpdate() > 0) {
			return true;
		}
		DBUtils.close(conn, ps, null);
		
		return false;
	}

	@Override
	public Map<String, Object> selectPage(String whereSql, int currentPage, int pageSize) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

}
